﻿#encoding: utf-8
=begin

Usage:  tutem libs

#计划函数采用驼峰命名法
#文件采取中间减号连接小写字符
#变量尽量短，需要就用下划线连接

=end

require 'time'
require 'date'
require 'rest-client'
require 'json'

load 'wechat_api.rb'
load 'rds_api.rb'
load 'mta_api.rb'

@rds = @rds_tt

def anonymousString string
    start_index = string.length / 3
    end_index = start_index * 2
    string[start_index..end_index] = "*" * (end_index - start_index + 1)
    string
end

def goods_short_names
    return @goodsShortNames if @goodsShortNames

    @goodsShortNames = {}
    sql = "select id, short_name from tutem_mall.eb_store_product;"
    queryRds(sql).each do |q|
        @goodsShortNames[q['id']] = q['short_name']
    end
    @goodsShortNames
end

def get_dir_path(dir)
    dir = dir.empty? ? "." : "./#{dir}/"
    OS.windows? ? dir.tr('/', '\\') : dir
end

def append_lines_to_fn lines, fn, dir = 'log'
    File.open(get_dir_path(dir) + fn,"a:UTF-8") { |f| f.write lines.join("\n")+"\n" }
end

def update_spread_count
    print "updating spread count for all tutem spreader\n"

    # 使用一个查询获取所有spread_uid的计数
    sql = "SELECT spread_uid, COUNT(*) as spread_count FROM tutem_mall.eb_user WHERE spread_uid > 0 GROUP BY spread_uid;"
    results = queryRds(sql)

    # 创建一个数组来存储所有的更新语句
    sqls = []
    results.each do |result|
        spread_uid = result['spread_uid']
        spread_count = result['spread_count']
        print '.'
        sqls << "UPDATE tutem_mall.eb_user SET spread_count = #{spread_count} WHERE uid = #{spread_uid};"
    end

    # 批量提交所有的更新语句
    commitTrans sqls

    puts "\ndone. #{sqls.size}"
end


def confirmOrderArrival order_id
    res = RestClient.get "https://mall.totembio.cn/api/confirm_arrival?order_id=#{order_id}&api_key=#{ENV['PSI_PASSWORD']}"
    return res
end

def confirm_arrival after_days

    seven_days_ago = Time.now.to_i - after_days*24*60*60
    order_ids = []
    sql = "select id from tutem_mall.eb_store_order where paid = 1 and pay_time < #{seven_days_ago} and status = 1 and refund_status = 0;"
    res = queryRds sql
    res.each { |q| order_ids += [q['id']] }

    puts "confirm delivery for orders:"
    order_ids.each do |order_id|
        print ".#{order_id}."
        res = RestClient.get "https://mall.totembio.cn/api/confirm_arrival?order_id=#{order_id}&api_key=#{ENV['PSI_PASSWORD']}"
    end

    puts "\ndone. #{order_ids.size}"

end

def notify_orders_to_wework

    print "notify orders to wework"

    pname = goods_short_names

    sql = "select o.id, o.uid, u.spread_uid as spreader_uid, o.notification, o.cart_id, o.status, o.real_name as user_name, o.user_phone, o.user_address, o.tags, o.pay_price, o.pay_time, u.real_name, u.level, u.nickname, o.mark, o.remark, o.rep_code from tutem_mall.eb_store_order o left join tutem_mall.eb_user u on (o.uid = u.uid) where o.paid = 1 and o.notification not like '%ww%' and o.order_code like '%FC%';"

    idx = 0
    queryRds(sql).each do |q|

        status = 'n/a'
        status = '待发货' if q['status'] == 0
        status = '已发货' if q['status'] == 1
        status = '已收货' if q['status'] == 2
        status = '已完成' if q['status'] == 3

        ptime = Time.at(q['pay_time']).strftime('%Y-%m-%d %H:%M:%S')

        nickname = q['nickname'].to_s
        nickname = q['real_name'].to_s if nickname == '微信用户'

        if q['tags'].include?('NPO')
            repcode = q['rep_code']
            if ( repcode != 'SYS' && repcode != 'UNS' )
                uid = q['uid']
                spreader_uid = q['spreader_uid']
                assignee_type = 'repCODE'
                task_code = 'crep-follow-npo-order'
                task_title = '请提示客户有首患订单，然后更新用户信息'
                channels = 'Wework Xiaoteng'
                entry = "https://totemlife.cn/apps/crm/create-user-profile?uid=#{uid}"
                description = "REP-#{repcode}的客户C##{spreader_uid}有新患U##{uid}提交了首单"
                status = 'pending'
                assign_task(assignee_type, repcode, task_code, task_title, channels, entry, description, status)
            end
        end

        mark_down = "## No.#{q['id']}"
        q['tags'].split(' ').each do |tag|
            if tag.include?('NPO') || tag.include?('R9U') || tag.include?('^')
                mark_down += " <font color='green'>#{tag}</font> " if tag.include? 'NPO'
                mark_down += " <font color='warning'>#{tag}</font> " if tag.include? 'R9U' #满九盒
                mark_down += " <font color='warning'>#{tag}</font> " if tag.include? '^'
            else
                mark_down += " #{tag}"
            end
        end
        mark_down += "\n"
        mark_down += "U##{sprintf('%05d',q['uid'])} #{nickname}"
        if q['level'] == 3
            mark_down += " <font color='warning'>[专业会员]</font>\n"
        else
            mark_down += " C##{sprintf('%05d',q['spreader_uid'])}\n"
        end

        mark_down +="#{ptime} #{status}\n"

        mark_down += "<font color='warning'>#{q['mark']}</font>\n" if q['mark'].to_s != ''
        mark_down += "<font color='green'>#{q['remark']}</font>\n" if q['remark'].to_s != ''

        cart_ids = eval q['cart_id']
        cart_id_list = cart_ids.join(',')

        sql_sales = "select product_attr_unique, cart_num, product_id from tutem_mall.eb_store_cart where id in (#{cart_id_list});"
        queryRds(sql_sales).each do |s|
            attr_unique = s['product_attr_unique']
            sku_name = skuList[attr_unique]
            sku_name = sku_name.to_s
            begin
                product_name = pname[s['product_id']] + " | " + sku_name
                product_name = "其它产品(p##{s['product_id']})" if product_name.nil?
                mark_down += "> #{product_name} x #{s['cart_num']}\n"
            rescue => e
                puts "ERROR:#{e.to_s}"
            end
        end

        user_phone = anonymousString q['user_phone'].to_s;
        user_address = anonymousString q['user_address'].to_s;

        mark_down += "### \n"
        mark_down += "### #{q['user_name']} #{user_phone}\n"
        mark_down += "### #{user_address}\n" if user_address != ''


        idx += 1
        send_message_to_bot 'WWBOT_XT', mark_down, ['@all']

        print '.'

        nots = q['notification'].split(' ')
        nots += ['ww'] #already notified with wework
        noti = nots.uniq.join(' ')

        sqlu = "update tutem_mall.eb_store_order set notification = '#{@rds_tt.escape noti}' where id = #{q['id']};"
        queryRds sqlu

    end
    puts "\ndone. #{idx}"

end

#现在使用eb_unify_pcode表里的冗余数据
def standardServeDaysForPcode
    if @standardServeDaysForPcode.nil?
        @standardServeDaysForPcode = {}
        sql = "select * from tutem_mall.eb_unify_pcode"
        (queryRds(sql)).each do |q|
            pav_barcode = q['pav_barcode'] #'可能是TTYX-12-xxxx'这种形式
            pcode = q['pav_barcode'].split('-').first
            @standardServeDaysForPcode.store pcode, q['serve_days'] if pav_barcode == pcode + '-1' #只取类似TTYX-1对应的标准服用量
        end
    end
    return @standardServeDaysForPcode
end

def getStandardServeDaysForPcode pcode
    days = standardServeDaysForPcode[pcode]
    return 0 if days.nil?
    return days
end

def genUserReportDuring pst, pet

    mark_down = "[#{Time.at(pst).strftime('%Y-%m-%d')},#{Time.at(pet).strftime('%Y-%m-%d')})\n"

    #新增用户
    uids = []
    sql = "select uid from tutem_mall.eb_user where add_time >= #{pst} and add_time < #{pet}"
    (queryRds(sql)).each { |q| uids += [q['uid']] }

    members_by_agent = {}  #推广者推荐的新用户组，以spreader为分组
    members_by_user = {} #非推广者推荐的新用户组以spreader为分组
    total_from_agent = 0
    bought_from_agent = 0
    total_from_user = 0
    bought_from_user = 0
    getUserListByUids(uids).each do |k, v|
        spreader_uid = v['spreader_uid']
        spreader_type = v['spreader_type']
        if spreader_type > 0
            total_from_agent += 1
            bought_from_agent += 1 if v['pay_count'] > 0
            if members_by_agent[spreader_uid].nil?
                members_by_agent.store spreader_uid, [v]
            else
                members = members_by_agent[spreader_uid]
                members += [v]
                members_by_agent.store spreader_uid, members
            end
        else
            total_from_user += 1
            bought_from_user += 1 if v['pay_count'] > 0
            if members_by_user[spreader_uid].nil?
                members_by_user.store spreader_uid, [v]
            else
                members = members_by_user[spreader_uid]
                members += [v]
                members_by_user.store spreader_uid, members
            end
        end
    end

    mark_down += "## 新增访客#{uids.size} 成交#{bought_from_agent+bought_from_user}\n"

    mark_down += "### 渠道推荐#{total_from_agent} 成交#{bought_from_agent}\n"
    members = members_by_agent.sort.to_h
    members.each do |spreader_uid, users|
        mark_down += "[#{sprintf('%05d',spreader_uid)}] #{userList[spreader_uid]['rep_code']} "
        #bought = 0
        users.each do |user|
            #bought += 1 if user['pay_count'] > 0
            tags = user['pay_count']>0 ? '**' : ''  #购买过的客户打上强调符号标记
            tag1 = user['login_type']=='mta' ? '^' : ''
            tag2 = user['is_qp']==1 ? '!' : ''
            tage = user['pay_count']>0 ? '**' : ''  #购买过的客户打上标记
            mark_down += "#{tags}#{tag1}#{user['uid']}#{tag2}#{tage},"
        end
        eom = mark_down.size
        mark_down = mark_down[0..eom-2] if eom >= 1 and mark_down[eom-1] == ',' #截去最后一个逗号
        mark_down += "\n"
        #mark_down += "#{bought}/#{users.size} "
    end

    mark_down += "### 用户转介#{total_from_user} 成交#{bought_from_user}\n"
=begin
    members = members_by_user.sort.to_h
    members.each do |spreader_uid, users|
        mark_down += "[#{sprintf('%05d',spreader_uid)}] "
        #bought = 0
        users.each do |user|
            #bought += 1 if user['pay_count'] > 0
            tag = user['pay_count']>0 ? '^,' : ','  #购买过的客户打上标记
            mark_down += "#{user['uid']}#{tag}"
        end
        #mark_down += "#{bought}/#{users.size} "
        eom = mark_down.size
        mark_down = mark_down[0..eom-2] if eom >= 1 and mark_down[eom-1] == ',' #截去最后一个逗号
        mark_down += "\n"
    end
=end

    #昨日下单用户
    uids = []
    sql = "select uid from tutem_mall.eb_store_order where paid = 1 and pay_time > #{pst} and pay_time < #{pet};"
    (queryRds(sql)).each { |q| uids += [q['uid']] }
    mark_down += "## 下单用户#{uids.size}\n"

    #昨日新增首单
    uids = []
    sql = "select uid from tutem_mall.eb_store_order where paid = 1 and pay_time > #{pst} and pay_time < #{pet} and tags like '%NPO%';"
    (queryRds(sql)).each { |q| uids += [q['uid']] }

    mark_down += "## 新增首单#{uids.size}\n"
=begin
    getUserListByUids(uids).each do |k, v|
        last_order_goods = ''
        arr = v['last_purchased'].split(' ') #yyyy-mm-dd {good: number}
        last_order_goods = arr[1] if arr.size > 1
        is_doctor = v['promoter_type'] == 1 ? '🥼' : ''
        mark_down += "[#{sprintf('%05d',v['spreader_uid'])}] ##{sprintf('%05d',v['uid'])} #{v['order_contact']} #{last_order_goods} #{is_doctor}\n"
    end
=end

    #新苏醒用户
    uids = get_wakeup_uids_by pst
    mark_down += "## 苏醒用户#{uids.size}\n"
    getUserListByUids(uids).each do |k, v|
        last_order_goods = ''
        arr = v['last_purchased'].split(' ') #yyyy-mm-dd {good: number}
        last_order_goods = arr[1] if arr.size > 1
        is_doctor = v['promoter_type'] == 1 ? '🥼' : ''
        mark_down += "[#{sprintf('%05d',v['spreader_uid'])}] ##{sprintf('%05d',v['uid'])} #{v['order_contact']} #{last_order_goods} #{is_doctor}\n"
    end

    #新沉睡用户
    uids = get_slept_uids_by pst
    mark_down += "## 入睡用户#{uids.size}\n"
    getUserListByUids(uids).each do |k, v|
        is_doctor = v['promoter_type'] == 1 ? '🥼' : ''
        last_purchased = (v['last_purchased'].split)[1] #yyyy-mm-dd {'ttys':1}
        mark_down += "[#{sprintf('%05d',v['spreader_uid'])}] ##{sprintf('%05d',v['uid'])} #{v['order_contact']} #{last_purchased} #{is_doctor}\n"
    end

    #昨日新增专业会员渠道，未来普通会员被批准为is_promoter应该有一个单独的记录表
    uids = []
    sql = "select uid from tutem_mall.eb_user where add_time > #{pst} and add_time < #{pet} and promoter_type = 1"
    (queryRds(sql)).each { |q| uids += [q['uid']] }

    mark_down += "# 新增诊疗专业会员#{uids.size}\n"
    getUserListByUids(uids).each do |k, v|
        is_doctor = v['promoter_type'] == 1 ? '🥼' : ''
        mark_down += "[#{sprintf('%05d',v['rep_id'])}] ##{sprintf('%05d',v['uid'])} #{v['agency']} #{v['real_name']} #{is_doctor}\n"
    end

    return mark_down

end

#发现一个问题，就是answers被存进去的时候都被格式化为\u十六进制code了，这里会重新格式化它，以便未来校正followup_options时直接对字符做比较
def recode_followup_answers

    print "Recoding followup answers: "
    sql = "select id, answers from tutem_mall.eb_follow_up where status = 1"
    usqls = []
    (queryRds(sql)).each do |q|
        fid = q['id']
        answers = JSON.parse q['answers']
        if answers.to_json != q['answers']
            print '*'
            sql = "update tutem_mall.eb_follow_up set answers = '#{@rds.escape answers.to_json}' where id = #{fid};"
            usqls += [sql]
        else
            print '.'
        end
    end
    commitTrans usqls
    puts "done\n"
end
#recode_followup_answers

#打印出所有的选项分类表，准备做统一，例如本人，统一应该改为自己：免疫相关或不明原因都改成自身体免疫
def report_followup_options

    fsql = "select id, answers, summary, submit_time, from_unixtime(submit_time) as submit_at from tutem_mall.eb_follow_up where status = 1 order by id desc"
    key_list = []
    all_answers = {}
    (queryRds(fsql)).each do |f|
        answers = JSON.parse f['answers']
        answers.each do |k, v|
            key_list += [k]
            values = all_answers[k].nil? ? [] : all_answers[k]
            if v.class == Array
                v.each do |e|
                    values += [e]
                    values = values.uniq
                end
            else
                values += [v]
                values = values.uniq
            end
            all_answers.store k, values
        end
    end

    #key_list = key_list.uniq
    #puts key_list.to_s

    #["usage", "gender", "channel", "purposes", "review", "stores"].each do |key|
    #
    ["usage", "gender", "age", "goods", "channel", "purposes", "start_time", "daily_serve", "tooktime", "review", "canShare", "dailyServe"].each do |key|
        puts "#{key}: #{all_answers[key].to_s}"
    end

end

def rename_followup_answers old, new
    usqls = []
    fsql = "select id, answers from tutem_mall.eb_follow_up where status = 1 and answers like '%#{old}%'"
    (queryRds(fsql)).each do |f|
        fid = f['id']
        answers = f['answers']
        answers = answers.gsub(old, new)
        sql = "update tutem_mall.eb_follow_up set answers = '#{@rds.escape answers}' where id = #{fid}"
        usqls += [sql]
    end
    commitTrans usqls
end

def trimed_purposes inputs
    outputs = []
    inputs.each do |i|
        o = i
        case i
        when '自身免疫性或难治性癫痫'
            o = '免疫相关或难治性癫痫'
        when '其他'
            o = '其他用途'
        else
            o = i
        end
        outputs += [o]
    end
    return outputs.uniq
end

def trimed_usages inputs
    outputs = []
    inputs.each do |i|
        o = i
        case i
        when '本人使用'
            o = '自己使用'
        else
            o = i
        end
        outputs += [o]
    end
    return outputs.uniq
end

def trimed_channels inputs
    return inputs.uniq
end

def get_followup_tags_for uid

    tags = []

    #获取随访问卷中的使用者和使用目的信息
    purposes = []
    usages = []
    fsql = "select id, answers, summary, submit_time, from_unixtime(submit_time) as submit_at from tutem_mall.eb_follow_up where uid = #{uid} and status = 1 order by id desc"
    (queryRds(fsql)).each do |f|

        answers = JSON.parse f['answers']

        ps = answers['purposes']
        purposes = purposes + ( ps.class == String ? [ps] : ps )          #在目前的图腾之友中purposes是String,需要单独转为数组一次

        ug = answers['usage']
        usages += [ "#{ug}使用"]

    end

    usages = trimed_usages usages
    purposes = trimed_purposes purposes

    purposes.each { |p| tags += [p] }
    usages.each { |p| tags += [p] }

    tags += usages
    tags += purposes

    tags = tags.uniq

    return tags

end

def get_comment_tags_for uid

    #获取商品评价中的使用者和使用目的信息
    reviewed = []
    purposes = []
    usages = []
    channels = []
    fsql = "select id, comment, product_score, service_score from tutem_mall.eb_store_product_reply where uid = #{uid} order by id desc"
    (queryRds(fsql)).each do |f|

        reviewed += ["产品评价#{f['product_score']}分"]
        reviewed += ["服务评价#{f['product_score']}分"]
        comment = f['comment']
        u1 = comment.split('分，经')
        if u1.size == 2
            u2 = u1[1].split('了解，为')
            if u2.size == 2
                channels += [ "#{u2[0]}" ]
                u3 = u2[1].split('购买，针对')
                if u3.size == 2
                    usages += [ "#{u3[0]}使用" ]
                    u4 = u3[1].split('使用，到目前')
                    if u4.size == 2
                        purposes += [ "#{u4[0]}"] 
                    end
                end
            end
        end

    end

    usages = trimed_usages usages
    purposes = trimed_purposes purposes
    channels = trimed_channels channels

    tags = (reviewed + usages + purposes + channels).uniq

    return tags

end

def get_program_tags_for uid

    tags = []

    sql = "SELECT h.id, h.uid, h.view_name, h.target_id, p.code, p.name FROM tutem_mall.eb_spread_history h left join tutem_mall.eb_tupal_program p on (h.target_id = p.id) where h.view_name = 'tupal' and uid = #{uid} group by target_id;"

    (queryRds(sql)).each do |q|
        code = "V-#{q['code']}"
        tags += [code]
    end

    tags = tags.uniq

    return tags

end

def get_tupal_enroll_tags_for uid

    tags = []

    sql = "SELECT e.id, e.uid, e.program_id, e.status, p.code from tutem_mall.eb_tupal_enroll e left join tutem_mall.eb_tupal_program p on (e.program_id = p.id) where e.uid = #{uid} and e.status != 'DRAFT'"

    (queryRds(sql)).each do |q|
        code = "#{q['code']}-#{q['status']}"
        tags += [code]
    end

    tags = tags.uniq

    return tags

end

#这个ucond通常可以是类似 select uid from tutem_mall.eb_xxx where xxxx
def update_profile_by ucond = ''

    where = ''
    where = "where u.uid in (#{ucond})" if ucond != ''
    sql = "select u.uid, u.status, w.subscribe from tutem_mall.eb_user u left join tutem_mall.eb_wechat_user w on (u.uid = w.uid) #{where}"

    usqls = []
    (queryRds(sql)).each do |q|
        uid = q['uid']
        profile_tags = []
        #profile_tags += ['恶意用户'] if q['status'] == 0
        profile_tags += get_program_tags_for uid
        profile_tags += get_tupal_enroll_tags_for uid
        profile_tags += get_wakeup_tags_for uid
        profile_tags += get_slept_tags_for uid
        #profile_tags += get_followup_tags_for uid
        #profile_tags += get_comment_tags_for uid
        profile_tags = profile_tags.uniq
        usql = "update tutem_mall.eb_user set profile = '#{@rds.escape profile_tags.join(' ')}' where uid = #{uid}"
        queryRds usql
        usqls += [usql]
    end

    #commitTrans usqls
    return usqls

end

def update_profile_in ids_selection = ''
   return update_profile_by ids_selection
end

def update_profile_for_uid uid = 0
    ucond = "select uid from tutem_mall.eb_user"
    ucond = "select #{uid}" if uid > 0
    update_profile_by ucond
end

#改造为两段
#一段是更新eb_user中的profile
#第二段是同步到企业微信中
def sync_profile_for_uid uid
    update_profile_for_uid uid
    sync_profile_to_wework uid
    return userList[uid]
end

def sync_profile_to_wework uid

    info = userList[uid]
    return info #暂不同步企业微信标签

    #获得客户的企业微信ID
    external_userid = info['external_userid']
    get_wework_external_contact_details external_userid

    #获得客服的企业微信ID
    follow_userid = info['follow_userid']
    userid = info['follow_userid']

    #如果客户企业微信联系人号和客服企业微信号均为空则不操作
    return if external_userid == '' || userid == ''

    #增加一个备注和摘要的准备同步到用户的企业微信 - 如果存在的话
    user_name = info['user_name']
    user_agency = info['user_agency']
    profile_info = JSON.parse info['profile']
    last_purchased = info['last_purchased']

    remark = "##{uid} #{user_agency} #{user_name}"
    description = "#{profile_info} #{last_purchased}"

    #修改用户昵称及备注
    #实际不重置
    #remark_wework_external_contact userid, external_userid, remark, description

    mark_wework_tag userid, external_userid, add_tags, remove_tags

    return info

end

def report_follow_up
    puts "report new followup"
    sql = "select f.*, from_unixtime(f.submit_time) as submit_at, u.is_promoter from tutem_mall.eb_follow_up f left join tutem_mall.eb_user u on (f.uid = u.uid) where f.status = 1 and f.notification not like '%ww%'"
    res = queryRds sql
    sqls = []
    res.each do |r|

        id = r['id']
        uid = r['uid']

        admin_id = 3 #默认为广州客服
        rep_name = '广州客服'

        if r['summary'].include?('肿瘤') || r ['summary'].include?('癌')
            admin_id = 4
            rep_name = '大连客服'
        end

        if r['is_promoter'] == 1
            admin_id = 5
            rep_name = '诊疗销售'
        end

        report =  "uid##{uid}#{r['name']}\n"
        report += "#{r['submit_at'].to_s.split(' +')[0]}\n"
        report += "# 摘要\n"
        report += "#{r['summary']}\n"
        report += "> 跟进分配：#{rep_name}\n"
        send_message_to_bot 'WWBOT_XT', report, ['@all']

        sql = "update tutem_mall.eb_follow_up set admin_id = #{admin_id}, notification = concat( notification, ' ww ')  where id = #{id}"
        sqls += [sql]

        sync_profile_for_uid uid

        print '.'

    end
    commitTrans sqls
    puts "\ndone"
end

def get_users_by_uids uids
    sql = "select uid, real_name, nickname, phone from tutem_mall.eb_user where uid in (#{uids.join(',')}) "
    users = {}
    res = queryRds sql
    res.each { |r| users.store r['uid'], r }
    return users
end

#对所有无成功推广用户的用户取消is_promoter和level
def disqualified_promoters

    #所有的推广者
    puids = []
    sql = "select uid from tutem_mall.eb_user where is_promoter = 1 "
    res = queryRds sql
    res.each { |r| puids += [r['uid']] }
    puts "current promoter: #{puids.size}"

    #所有的有效推广者
    vuids = []
    sql = "select spread_uid from tutem_mall.eb_user group by spread_uid "
    res = queryRds sql
    res.each { |r| vuids += [r['spread_uid']] }
    puts "actived promoter: #{vuids.size}"

    #所有没有做推广的uid
    iuids = []
    iuids = puids - vuids
    puts "inactived promoter: #{iuids.size}"

    #待取消promoter身份的uid
    the_uids = iuids
    duids = []
    users = {}
    sql = "select uid, real_name, nickname, phone, is_promoter from tutem_mall.eb_user where uid in (#{the_uids.join(',')}) and add_time < #{Time.now.to_i-60*24*60*60}"
    res = queryRds sql
    res.each do |r|
        duids += [r['uid']]
        users.store r['uid'], r
    end
    puts "disqualified promoter: #{duids.size}"

    return users

end

def createEventA

    xlsx = "tutem_event_a.xlsx"
    event = "A"
    title = "赠送2021中秋VIP礼券"
    sqls = []
    items = {}
    begin
        s = SimpleSpreadsheet::Workbook.read xlsx
        s.selected_sheet = s.sheets.first
        line_idx = 0
        updated_count = 0
        s.first_row.upto(s.last_row) do |line|

            line_idx += 1
            next if line_idx == 1
            isEventA = s.cell(line,11);
            next if isEventA == '1';

            item = {}

            phone = s.cell(line,9)
            name = s.cell(line,5)
            address = s.cell(line,10);
            item = { "title"=>title, "phone"=>phone, "name"=>name, "address"=>address}
            items.store phone, item
            data = @rds.escape item.to_json

            sql = "insert into tutem_mall.eb_sms_event(name,phone,data,create_time,event) values(
                '#{title}', '#{phone}', '#{data}', #{Time.now.to_i}, '#{event}')"
            sqls += [sql]

        end
    rescue => e
        puts "ERROR:#{e.to_s}"
    end

    #commitTrans sqls

    return items

end

def get_latest_daily_card_post day=''

    post = {}
    day_condition = day.empty? ? '' : "and slug like '#{day}%'"
    tag_id = '61d026332bdf0d81b1a5b329'
    sql = "select title, custom_excerpt as intro, slug, feature_image as image, published_at from tutem_blog.posts where id in ( select post_id FROM tutem_blog.posts_tags where tag_id = '#{tag_id}') #{day_condition} order by published_at desc limit 1;"
    recs = queryRds sql
    recs.each { |r| return r }
    return post

end

def daily_card_trigger date = Time.now.strftime("%Y-%m-%d"), url = 'https://totemlife.cn/apps/x/daily'

    now = Time.now
    return if now.hour > 12 #超过凌晨1点就不再更新了

    event_code = 'DAY-' + date
    # 定义文本模板
    template = <<-TEXT
🚀 图腾生命每日知识打卡

📅 #{date}
📚 点击链接打卡领取金豆！

➡ <a href='https://totemlife.cn/apps/ci2?ec=DAY-#{date}&url=#{url}'>打卡今日资讯主题</a>

🌟 打开主题后转发分享
💞 传递越多，奖励越多！
TEXT

    sql = "UPDATE `tutem_mall`.`eb_mp_follow_rule` SET `title` = '#{event_code}', `description` = \"#{@rds.escape template}\", `status` = 1 WHERE `id` = 10; "
    queryRds sql
    #sql = "update tutem_mall.eb_daily_poster set comment = '#{title}' where day = '#{date}'"# and comment = ''"
    #queryRds sql

end

def award_points_to_user rec #returned with bill_id

    bill_id = 0

    uid = rec['uid']
    code = rec['code']
    bonus = rec['bonus']
    channel = rec['channel']
    brief = rec['brief']
    title = rec['title']
    mark = rec['mark']
    weworkNotice = rec['weworkNotice'].nil? ? true : rec['weworkNotice']
    mpNotice = rec['mpNotice'].nil? ? true : rec['mpNotice']
    mpsRequired = rec['mpsRequired'].nil? ? true : rec['mpsRequired'] #必须要求关注公众号, 默认为必须

    msg = "awarding #{uid} with #{bonus} for #{title} as #{code}"
    puts msg

    openid = ''
    user_name = ''
    user_phone = ''
    p_balance = 0.00
    subscribe = 0

    sql = "select u.real_name, u.phone, w.subscribe, w.openid, u.integral as p_balance from tutem_mall.eb_user u left join tutem_mall.eb_wechat_user w on (u.uid = w.uid) where u.uid = #{uid};"
    (queryRds(sql)).each do |q|
        openid =  q['openid']
        p_balance = q['p_balance']
        subscribe =q['subscribe']
        user_name = q['real_name'].to_s
        user_phone = q['phone'].to_s
        break
    end
    if ( openid.to_s.empty? || subscribe == 0 ) && mpsRequired
        err = "[ABORT] openid is nil or not subscribed(#{subscribe}) when #{msg}"
        puts err
        send_message_to_bot 'WWBOT_XT', err, ['@all'] if weworkNotice
        return 0
    end

    awarded = 0
    sql = "select count(*) as awarded from tutem_mall.eb_user_bill where uid = #{uid} and link_id = '#{code}'"
    (queryRds(sql)).each do |q|
        awarded = q['awarded']
        break
    end
    if awarded > 0
        err = "[ABORT] already awarded for #{awarded} times when #{msg}"
        puts err
        send_message_to_bot 'WWBOT_XT', err, ['@all'] if weworkNotice
        return 0
    end

    now = Time.now.to_i
    sqls = []
    usql = "update tutem_mall.eb_user set integral = integral + #{bonus} where uid = #{uid};"
    sqls += [usql]
    usql = "INSERT INTO tutem_mall.eb_user_bill (`uid`, `link_id`, `pm`, `title`, `category`, `type`, `number`, `balance`, `mark`, `add_time`, `status`, `take`) 
      SELECT #{uid}, '#{code}', 1, '#{title}', 'integral', 'system_add', #{bonus}, integral, '#{mark}', #{now}, 1, 0 from tutem_mall.eb_user where uid = #{uid}"
    sqls += [usql]

    commit = commitTrans sqls

    if commit

        isql = "select id from tutem_mall.eb_user_bill where uid = #{uid} and link_id = '#{code}' and add_time = #{now} and number = #{bonus} order by id desc limit 1"
        (queryRds(isql)).each { |i| bill_id = i['id'] }

        balance = p_balance + bonus
        totem_points_change_notice  openid, channel, bonus if mpNotice

        ww_notice = "# 用户积分发放提醒 #{code}\n"
        ww_notice += "#{Time.now.strftime('%Y-%m-%d %H:%M:%S')}\n\n"
        ww_notice += "**[#{sprintf('%05d',uid)}] #{user_phone} #{user_name}**\n"
        ww_notice += "说明: #{brief}\n"
        ww_notice += "备注: #{mark}\n"
        ww_notice += "详情: #{title}\n"
        ww_notice += "获得: #{bonus}\n"
        ww_notice += "余额: #{balance.to_f.round(2)}\n\n"
        ww_notice += ">\n"
        send_message_to_bot 'WWBOT_XT', ww_notice, ['@all'] if weworkNotice

        puts "done"
        return bill_id
    end

    puts "[ERROR] something wrong when commit, please check log/rds_api.log"
    return 0

end

#针对某个用户更新其所有的订单统计、首单统计、数据来源是pickup
#uot: user_order_tags
def updateUserInfo uid

    first_order = true
    bought_goods = {}
    order_tags ={}
    rep_codes = {}
    actual_weixin_paid = 0
    actual_tupay_paid = 0
    order_count = 0
    last_purchased_date = ''
    last_purchased_info = ''
    buy_forecast = 3258806399   #2073-04-07 23:59:59 预测下次购买时间

    sql = "select p.oid as order_id, p.uid, p.pcode_info, p.rep_code, p.channel_info, p.goods_info, p.actual_paid, p.refunded_amount, p.pay_type, o.pay_time as pay_time, from_unixtime(o.pay_time) as paid_at, u.skip_np from tutem_mall.eb_pickup_book p left join tutem_mall.eb_store_order o  on p.oid = o.id left join tutem_mall.eb_user u on p.uid = u.uid where p.uid = #{uid} and o.paid = 1 order by pay_time asc"
    puts sql

    (queryRds(sql)).each do |s|

        order_count += 1
        tag_info = ""
        order_id = s['order_id']
        paid_at = s['paid_at']
        rep_code = s['rep_code']
        begin
            pcode_info = JSON.parse s['pcode_info']
        rescue => e
            puts "ERROR: order_id #{order_id}, pcode_info #{s['pcode_info']}"
            puts e
            return
        end
        last_purchased_date = Time.at(s['pay_time']).strftime('%Y-%m-%d')
        last_purchased_info = last_purchased_date + ' ' + s['pcode_info'].to_s
        skip_np = s['skip_np']

        if s['pay_type'] == 'weixin'
            actual_weixin_paid = actual_weixin_paid + s['actual_paid'] - s['refunded_amount']
        else
            actual_tupay_paid = actual_tupay_paid + s['actual_paid'] - s['refunded_amount']
        end

        pcode_info.each do |pcode, qty|

            bought_goods[pcode] = 0 if bought_goods[pcode].nil?
            prev_qty = bought_goods[pcode]
            curr_qty = prev_qty + qty

            bought_goods.store pcode, curr_qty

            tag_info += "#{pcode}(#{prev_qty},#{curr_qty}]"
            for idx in prev_qty..curr_qty
                next if idx  == prev_qty #跳过基础数量
                tag_info += (idx % 9 )==0 ? '^' : ''
            end
            tag_info += " "

            buy_forecast = s['pay_time'] + curr_qty * 10 * 24 * 60 * 60 #这里记得是10条

        end

        if first_order                                  #仅对第一张订单做NPO或者SKIP-NP标记
            tag_info += skip_np > 0 ? "SKIP-NP" : "NPO" #对于标注了跳过首单标注为SKIP-NP
            first_order = false
        end

        tag_info += " #{s['channel_info']}" if s['channel_info'] != ''

        puts "will tag order_id #{order_id} with tags #{tag_info}"
        order_tags.store order_id, tag_info
        rep_codes.store order_id, rep_code

    end

    usqls = []
    order_tags.each do |order_id, tag_info|
        usqls += ["update tutem_mall.eb_store_order set rep_code = '#{rep_codes[order_id]}', tags = '#{@rds.escape tag_info}' where id = #{order_id} and uid = #{uid}"]
    end

    commitTrans usqls

    #在这里更新用户的购买历史

    bought_info = "已购#{order_count}次，实付#{actual_weixin_paid.to_f.round(2)}元，兑换#{actual_tupay_paid.to_f.round(0)}图贝"
    bought_arr = []
    bought_goods.each do |pcode, qty|
        bought_arr += ["#{pcode}*#{qty}"]
    end

    goods_list = bought_arr.to_s
    goods_list = '[...]' if goods_list.size > 255
    sql = "update tutem_mall.eb_user set purchased = '#{@rds.escape bought_info}', actual_paid = #{(actual_weixin_paid + actual_tupay_paid).to_f.round(2)},last_purchased = '#{@rds.escape last_purchased_info}', goods_list = '#{goods_list}', buy_forecast = #{buy_forecast} where uid = #{uid}"
    #queryRds(sql)
    sql = "update tutem_mall.eb_user set purchased = ?, actual_paid = ?,last_purchased = ?, goods_list = ?, buy_forecast = ? where uid = ?"
    executeRds(sql, bought_info, (actual_weixin_paid + actual_tupay_paid).to_f.round(2), last_purchased_info, goods_list, buy_forecast, uid)

    for_uids = " uid in (#{uid})"
    updateQP for_uids
end

def update_pcode_info_in_pickup_book order_id

    puts "Update pickup book pcode info for O#{order_id}"
    pname = goods_short_names

    sql = "select id, uid, cart_id, pay_type, refund_price, pay_time from tutem_mall.eb_store_order where id = #{order_id} and paid = 1"
    cart_ids = []
    pay_type = ''
    uid = 0
    pay_time = 0
    refunded_amount = 0.00;
    (queryRds(sql)).each { |r| cart_ids = eval r['cart_id']; pay_type = r['pay_type']; pay_time = r['pay_time']; refunded_amount = r['refund_price']; uid = r['uid'] }
    cart_id_list = cart_ids.join(',')

    total_standard_qty = 0
    total_actual_paid = 0.00

    goods = []
    pcode_info = {}

    sql_sales = "select id, product_attr_unique, cart_num, product_id from tutem_mall.eb_store_cart where id in (#{cart_id_list});"
    queryRds(sql_sales).each do |s|

        attr_unique = s['product_attr_unique']
        attr_barcode = '' #等待到原始订单数据中去找真实的规格barcode

        units = 1
        pcode = 'MISSING'

        barcode = barcodeList[attr_unique]
        barcode = 'MISSING-1' if barcode.nil?
        # TTYS-3：指图腾益生标准粉剂，这个规格包括3个最小售卖单位
        bar_array = barcode.split('-')
        if bar_array.size >= 2
            pcode = bar_array[0]
            units = bar_array[1].to_i
            existed_number = pcode_info[pcode].nil? ? 0 : pcode_info[pcode] #同一张订单中可能会有多个标准代码的商品
            pcode_info.store pcode, existed_number + units * s['cart_num']
        end

        sku_name = skuList[attr_unique]
        sku_name = sku_name.to_s
        product_name = pname[s['product_id']] + "|" + sku_name

        standard_qty = units * s['cart_num']
        total_standard_qty =  total_standard_qty + standard_qty

        cart_id = s['id']
        csql = "SELECT i.oid AS order_id, i.cart_info AS cart_info, ( o.pay_price + o.deduction_price + o.coupon_price ) AS order_total_price, (o.deduction_price + o.coupon_price ) AS order_deduction
                  FROM tutem_mall.eb_store_order_cart_info i LEFT JOIN tutem_mall.eb_store_order o ON ( i.oid = o.id ) 
                  WHERE i.cart_id = #{cart_id} limit 1;"

        actual_paid = 0.00
        (queryRds(csql)).each do |r|
            cart_info = JSON.parse r['cart_info']
            attr_barcode = cart_info['productInfo']['attrInfo']['bar_code']
            total_price = cart_info['cart_num'] * cart_info['truePrice'] #真实价格
            to_deduct = 0.0
            if r['order_deduction'] > 0 && r['order_total_price'] > 0
                to_deduct = ( total_price * r['order_deduction'] ) / r['order_total_price']
            end
            actual_paid = total_price - to_deduct
            total_actual_paid += actual_paid
        end

        item = {}
        item.store "pcode", pcode
        item.store "attr_unique", attr_unique
        item.store "attr_barcode", attr_barcode
        item.store "standard_qty", standard_qty
        item.store "weixin_paid", pay_type == 'weixin' ? actual_paid.to_f.round(2) : 0.00
        item.store "tupay_paid", pay_type == 'yue' ? actual_paid.to_f.round(2) : 0.00
        item.store "product_id", s['product_id']
        item.store "product_name", product_name
        item.store "cart_num", s['cart_num']
        item.store "unit", units

        goods += [item]

    end

    money_paid = 0.00
    money_paid = total_actual_paid if pay_type == 'weixin'

    channel_info = 'P0T0-UNS'
    rep_code = 'UNS'
    sql = "select ur.user_id as channel_id, ur.rep_id, ur.rep_code, ur.age, u.is_promoter, u.promoter_type from tutem_mall.eb_user_rep ur left join tutem_mall.eb_user u on (ur.user_id = u.uid) where ur.user_id in (select spread_uid from tutem_mall.eb_user where uid = #{uid} and is_promoter = 0) and ( ur.since <= #{pay_time} and ur.until > #{pay_time} );"
    (queryRds(sql)).each do |q|
        rep_code = q['rep_code'].to_s
        rep_code = 'UNS' if rep_code.empty?
        channel_info = "P#{q['is_promoter']}T#{q['promoter_type']}-#{rep_code}"
    end
    #这里是处理用户本身为专业会员，但有上级推荐人的情况
    sql = "select ur.user_id as channel_id, ur.rep_id, ur.rep_code, ur.age, u.is_promoter, u.promoter_type from tutem_mall.eb_user_rep ur left join tutem_mall.eb_user u on (ur.user_id = u.uid) where ur.user_id in (select uid from tutem_mall.eb_user where uid = #{uid} and is_promoter > 0) and ( ur.since <= #{pay_time} and ur.until > #{pay_time} );"
    (queryRds(sql)).each do |q|
        rep_code = q['rep_code'].to_s
        rep_code = 'UNS' if rep_code.empty?
        channel_info = "P#{q['is_promoter']}T#{q['promoter_type']}-#{rep_code}"
    end

    usql = "update tutem_mall.eb_pickup_book set actual_paid = #{total_actual_paid}, refunded_amount = #{refunded_amount}, pay_type = '#{pay_type}',
        goods_qty = #{total_standard_qty}, goods_info = '#{@rds.escape goods.to_json}', channel_info = '#{channel_info}',
        pcode_info = '#{@rds.escape pcode_info.to_json}', rep_code = '#{rep_code}' where oid = #{order_id};"
    queryRds usql

end

#这个用来测试分时间段标记REP模式
def pickup_goods_info_updater_test
    sql = "select id from tutem_mall.eb_store_order where paid  = 1 and uid in (SELECT uid from tutem_mall.eb_user where spread_uid = 2303)"
    order_ids = []
    (queryRds(sql)).each { |s| order_ids += [s['id']] }
    #order_ids = [6497,6498]
    order_ids.each { |order_id| update_pcode_info_in_pickup_book order_id }
end

def update_all_pickup_goods_info more=""
    sql = "select id from tutem_mall.eb_store_order where paid  = 1 #{more}"
    order_ids = []
    (queryRds(sql)).each do |s|
        order_ids += [s['id']]
    end

    order_ids.each do |order_id|
        update_pcode_info_in_pickup_book order_id
    end
end

#对所有尚未增加商品信息的分拣单登记商品信息
def update_pickup_goods_info
    #sql = "select oid from tutem_mall.eb_pickup_book where goods_info is NULL"
    sql = "select oid from tutem_mall.eb_pickup_book where pcode_info ='' "
    order_ids = []
    (queryRds(sql)).each do |s|
        order_ids += [s['oid']]
    end

    order_ids.each do |order_id|
        update_pcode_info_in_pickup_book order_id
    end
end

def update_pickup_goods_info_by uid
    sql = "select oid from tutem_mall.eb_pickup_book where uid = #{uid} "
    order_ids = []
    (queryRds(sql)).each do |s|
        order_ids += [s['oid']]
    end

    order_ids.each do |order_id|
        update_pcode_info_in_pickup_book order_id
    end
end

#对没有tags的已支付订单，根据用户uid号在eb_store_order中产生user_order_tags
def update_uot
    sql = "select uid from tutem_mall.eb_store_order where tags = '' and paid = 1 group by uid"
    (queryRds(sql)).each do |s|
        uid = s['uid']
        updateUserInfo uid
    end
end

def gen_user_tag_set_sql uid, tag, action = 'add'
    sql = "select uid, profile from tutem_mall.eb_user where uid = #{uid}";
    tags = []
    (queryRds(sql)).each do |q|
        begin
            tags = JSON.parse q['profile']
        rescue => e
            puts e
        end
    end

    tags -= [tag]
    tags += [tag] if action == 'add'
    tags = tags.uniq

    sql = "update tutem_mall.eb_user set profile = '#{@rds.escape tags.to_json}' where uid = #{uid}"
    return sql
end

#标记新苏醒用户
def get_wakeup_uids_by theTime = Time.now.to_i

    #theTime = Time.parse(day.to_s + " 00:00:00").to_i
    day30Ago = theTime - 30*24*60*60
    day90Ago = theTime - 90*24*60*60
    day120Ago = theTime - 120*24*60*60
    day180Ago = theTime - 180*24*60*60

    #只返回最后一天的苏醒用户列表
    uids = []
    sql = "select uid from tutem_mall.eb_user where
        uid in ( select uid from tutem_mall.eb_store_order where paid = 1 and pay_time >= #{theTime}) and
        uid not in ( select uid from tutem_mall.eb_store_order where paid = 1 and pay_time >= #{day90Ago} and pay_time < #{theTime}) and
        uid in ( select uid from tutem_mall.eb_store_order where paid = 1 and pay_time < #{day90Ago})"

    (queryRds(sql)).each { |q| uids += [q['uid']] }

    return uids

end

def get_wakeup_tags_for uid, theTime = Time.now.to_i

    tags = []

    #theTime = Time.parse(day.to_s + " 00:00:00").to_i
    day30Ago = theTime - 30*24*60*60
    day90Ago = theTime - 90*24*60*60
    day120Ago = theTime - 120*24*60*60
    day180Ago = theTime - 180*24*60*60

    #自指定时刻至其30天前有购买，但再之前的90天内没有购买，而再之前有购买，则打上新唤醒标志
    sql = "select uid from tutem_mall.eb_user where uid = #{uid} and 
        uid in ( select uid from tutem_mall.eb_store_order where paid = 1 and pay_time >= #{day30Ago} and  pay_time < #{theTime} ) and 
        uid not in ( select uid from tutem_mall.eb_store_order where paid = 1 and pay_time >= #{day120Ago} and pay_time < #{day30Ago}) and 
        uid in ( select uid from tutem_mall.eb_store_order where paid = 1 and pay_time < #{day120Ago})"
    (queryRds(sql)).each { |q| tags += ['新苏醒用户'] }

    return tags

end

def remove_profile_tag tag

    uids = []
    sql = "select uid from tutem_mall.eb_user where profile like '%#{tag}%'"
    (queryRds(sql)).each { |q| uids += [q['uid']] }

    usqls = []
    uids.each do |uid|
        sql = gen_user_tag_set_sql uid, tag, 'remove'
        usqls += [sql]
    end
    commitTrans usqls

end

def get_slept_uids_by theTime = Time.now.to_i, spreader_uid = 0

    day30Ago = theTime - 30*24*60*60
    day90Ago = theTime - 90*24*60*60
    day120Ago = theTime - 120*24*60*60
    day180Ago = theTime - 180*24*60*60

    #只返回最后一天的新沉睡用户列表
    uids = []
    sql = "select uid from tutem_mall.eb_user where
        uid in ( select uid from tutem_mall.eb_store_order where paid = 1 and pay_time < #{day90Ago} and pay_time > #{day90Ago-24*60*60})
        and uid not in ( select uid from tutem_mall.eb_store_order where paid = 1 and pay_time >= #{day90Ago})"
    sql += " and spread_uid = #{spreader_uid}" if spreader_uid != 0
    (queryRds(sql)).each { |q| uids += [q['uid']] }
    return uids

end

def get_slept_tags_for uid, theTime = Time.now.to_i

    tags = []

    day30Ago = theTime - 30*24*60*60
    day90Ago = theTime - 90*24*60*60
    day120Ago = theTime - 120*24*60*60
    day180Ago = theTime - 180*24*60*60

    #三个月前买过，但最近三个月没有买了，称之为新沉睡用户
    sql = "select uid from tutem_mall.eb_user where uid = #{uid} and 
        uid in ( select uid from tutem_mall.eb_store_order where paid = 1 and pay_time < #{day90Ago})
        and uid not in ( select uid from tutem_mall.eb_store_order where paid = 1 and pay_time >= #{day90Ago})"
    (queryRds(sql)).each { |q| tags = ['新沉睡用户'] }

    #半年前买过，但最近半年没有买了，称之为沉睡多时
    sql = "select uid from tutem_mall.eb_user where uid = #{uid} and
        uid in ( select uid from tutem_mall.eb_store_order where paid = 1 and pay_time < #{day180Ago})
        and uid not in ( select uid from tutem_mall.eb_store_order where paid = 1 and pay_time >= #{day180Ago})"
    (queryRds(sql)).each { |q| tags = ['沉睡满半年'] }

    return tags

end

def updateAllUsersInfo
    sql = "select uid from tutem_mall.eb_store_order where paid = 1 group by uid"
    (queryRds(sql)).each { |s| updateUserInfo s['uid'] }
end

def report_questioned_promoters

    sql = "select u.uid, u.spread_uid as suid, u.is_promoter as isp, u.level as lvl, u.real_name, u.nickname, u.agency, u.account, u.phone, u.register_code, u.spread_count, u.now_money, u.integral, u.promoter_type from tutem_mall.eb_user u where ( u.is_promoter = 1 or u.level = 3)  and u.uid not in (select uid from tutem_mall.eb_user_level where level_id = 3) and account not like '%8000%'  order by u.promoter_type;"
    (queryRds(sql)).each do |q|
        report = "发现异常等级会员：用户##{q['uid']} P#{q['isp']} C#{q['suid']} L#{q['lvl']} #{q['real_name']} #{q['agency']} #{q['phone']}"
        send_message_to_bot 'WWBOT_TT', report, ['@all']
    end

end

def getRecruitInfoBySpreader st, et

    info_hash = {}

    sql = "select u.* from tutem_mall.eb_user u where u.add_time >= #{st} and u.add_time < #{et}"
    queryRds(sql).each do |q|

        suid = q['spread_uid']
        info = info_hash[suid].nil? ? {'recruited_count'=>0, 'recruited_list'=>[], 'mta_recruited_count'=>0, 'mta_recruited_list' => []} : info_hash[suid]

        info.store 'recruited_count', info['recruited_count'] + 1
        info.store 'recruited_list', (info['recruited_list'] += [q['uid']]).uniq
        if q['login_type'] == 'mta'
            info.store 'mta_recruited_list', (info['mta_recruited_list'] += [q['uid']]).uniq
            info.store 'mta_recruited_count', info['mta_recruited_count'] + 1
        end

        info_hash.store suid, info
    end

    return info_hash

end

def getSpreaderInfoFor spreaders, st, et

    return {} if spreaders.empty?
    hash = {}

    if spreaders.include? 0
        info = {}
        info.store 'promoter_type', 9
        info.store 'promoter_name', '吴尚极'
        info.store 'promoter_agency', '无上级推广人'
        info.store 'rep_id', 0
        info.store 'rep_code', 'UNS'
        info.store 'rep_team', 'SYSTEM'
        hash.store 0, info
    end

    sql = "select u.uid, u.is_promoter, u.promoter_type, u.agency, u.department, u.real_name, u.title, u.rep_id, u.rep_code, r.team as rep_team from tutem_mall.eb_user u left join tutem_mall.eb_rep r on (u.rep_id = r.id) where u.uid in (#{spreaders.join(',')})"
    (queryRds(sql)).each do |q|

        info = {}
        suid = q['uid']

        info.store 'promoter_type', q['promoter_type']
        info.store 'promoter_name', q['real_name'].to_s
        info.store 'promoter_agency', q['agency'].to_s

        rep_id = 0
        rep_code = 'UNS'
        rep_team = 'SYSTEM'

        isql = "select ur.*, er.team as rep_team from tutem_mall.eb_user_rep ur left join tutem_mall.eb_rep er on (ur.rep_id = er.id) where user_id = #{suid} and since <= #{st} and until > #{et}"
        (queryRds(isql)).each do |i|
            rep_id = i['rep_id']
            rep_code = i['rep_code']
            rep_team = i['rep_team']
        end

        info.store 'rep_id', rep_id
        info.store 'rep_code', rep_code
        info.store 'rep_team', rep_team

        hash.store suid, info

    end

    return hash

end

#这里是生成制定时间段的渠道销售报告的核心
def getOrderInfoBySpreader st, et

    info_hash = {}

    sql = "select o.*, u.spread_uid as spreader_uid from tutem_mall.eb_store_order o left join tutem_mall.eb_user u on o.uid = u.uid where o.paid = 1 and o.pay_time >= #{st} and o.pay_time < #{et}" #and o.refund_status = 0"
    queryRds(sql).each do |q|

        suid = q['spreader_uid']
        info = info_hash[suid].nil? ? { 'buyer_count'=>0, 'buyer_list'=>[],
                                        'order_count'=>0, 'order_list'=>[],
                                        'first_order_count'=>0, 'first_order_weixin_paid'=>0.00, 'first_order_tupay_paid'=>0.00, 'first_order_list'=>[],
                                        'qpo_count'=>0, 'qpo_list'=>[],
                                        'new_slept_count'=>0, 'new_slept_list'=>[],
                                        'actual_weixin_paid'=>0.00,'actual_tupay_paid'=>0.00,
                                        'npo_user_list' => [], 'qpo_user_list' => []
                                      } : info_hash[suid]

        # Update npo_user_list and qpo_user_list
        if q['tags'].include? 'NPO'
            info.store 'first_order_count', info['first_order_count'] + 1
            info.store 'first_order_weixin_paid', info['first_order_weixin_paid'] + (q['pay_price'] - q['refund_price']) if q['pay_type'] == 'weixin'
            info.store 'first_order_tupay_paid', info['first_order_tupay_paid'] + (q['pay_price'] - q['refund_price']) if q['pay_type'] == 'yue'
            info.store 'first_order_list', info['first_order_list'] += [q['id']]
            info.store 'npo_user_list', (info['npo_user_list'] += [q['uid']]).uniq
        end

        if q['tags'].include? 'QPO'
            info.store 'qpo_count', info['qpo_count'] + 1
            info.store 'qpo_list', info['qpo_list'] += [q['id']]
            info.store 'qpo_user_list', (info['qpo_user_list'] += [q['uid']]).uniq
        end

        info.store 'buyer_list', (info['buyer_list'] += [q['uid']]).uniq
        info.store 'buyer_count', info['buyer_list'].size
        info.store 'order_count', info['order_count'] + 1
        info.store 'order_list', info['order_list'] += [q['id']]
        new_slept_list = get_slept_uids_by st, suid
        info.store 'new_slept_count', new_slept_list.size
        info.store 'new_slept_list', new_slept_list
        info.store 'actual_weixin_paid', info['actual_weixin_paid'] + (q['pay_price'] - q['refund_price']) if q['pay_type'] == 'weixin'
        info.store 'actual_tupay_paid', info['actual_tupay_paid'] + (q['pay_price'] - q['refund_price']) if q['pay_type'] == 'yue'

        info_hash.store suid, info
    end

    info_hash.each do |spreader, info|
        order_list = info['order_list']
        goods_paid = getGoodsPaidInWeixinForOrders order_list
        goods_paid.each do |pcode, paid|
            info.store pcode, paid
        end
        info_hash.store spreader, info
    end

    return info_hash

end

def getSpreaderDailyReport date = '2022-11-24'

    st = Time.parse(date + ' 00:00:00').to_i; et = st + 24*60*60;
    sdr_hash = {}

    info_hash = getRecruitInfoBySpreader st, et
    info_hash.each do |suid, info|
        sdr = sdr_hash[suid].nil? ? {} : sdr_hash[suid]
        sdr = sdr.merge(info)
        sdr_hash.store suid, sdr
    end

    info_hash = getOrderInfoBySpreader st, et
    info_hash.each do |suid, info|
        sdr = sdr_hash[suid].nil? ? {'recruited_count'=>0, 'recruited_list'=>[]} : sdr_hash[suid]
        sdr = sdr.merge(info)
        sdr_hash.store suid, sdr
    end

    spreaders = sdr_hash.keys
    info_hash = getSpreaderInfoFor spreaders, st, et
    info_hash.each do |suid, info|
        sdr = sdr_hash[suid]
        sdr = sdr.merge(info)
        sdr_hash.store suid, sdr
    end

    return sdr_hash

end

def typical_goods_codes
    return ['TTYX', 'TTYS', 'TTKFY', 'DDT', 'XESH', 'TTFZY', 'TTSZ', 'TTBYS' ] #all others named as OTHR
    #return ['XNPT','TTBP','FPLJ','FPLJSY','FPLJTY','YJPJP','YJPJK','YJPJB','YJPJJ','ZJY','HSCT','LHJRSY','TTKFY','TTYMJ','QEYJ','YSJDSM','YSTHT','MKYXTY','MKYXXY','LPKA','LPKATY','LPKB','TTYMB','DLDB','TTYX','TTYS','YSZJ','XESH','DDT','TTYBM','TTFZY','TTYBD', 'TTSZ' ]
end

def getGoodsQtyForOrders oids

    info = {}
    return info if oids.empty?

    goods = typical_goods_codes
    goods.each { |g| info.store g, 0 }

    sql = "select pcode_info from tutem_mall.eb_pickup_book where oid in (#{oids.join(',')});"
    res = queryRds sql
    res.each do |q|
        pcodes = JSON.parse q['pcode_info']
        pcodes.each do |pcode, sales|
            rpcode = pcode #rpcode是用于报告的pcode
            rpcode = 'OTHR' if !(goods.include? pcode) #未在定义列表中都当做OTHER来报告
            info.store(rpcode, 0) if info[rpcode].nil?
            prev_sales = info[rpcode]
            info.store rpcode, prev_sales + sales
        end
    end

    return info

end

def getGoodsPaidInWeixinForOrders oids

    pcodes = typical_goods_codes
    info = {}
    (pcodes + ['OTHR']).each { |pc| info.store pc, 0.0 }
    return info if oids.empty?

    pcodes.each { |pcode| info.store pcode, 0.0 }

    sql = "select goods_info from tutem_mall.eb_pickup_book where oid in (#{oids.join(',')});"
    res = queryRds sql
    res.each do |q|
        goods = JSON.parse q['goods_info']
        goods.each do |item|
            pcode = item['pcode']
            rpcode = pcode #rpcode是用于报告的pcode
            rpcode = 'OTHR' if !(typical_goods_codes.include? pcode) #未在定义列表中都当做OTHER来报告
            #info.store(rpcode, 0.00) if info[rpcode].nil? #这句话原则上是可以不要的，因为已经针对所有typical_pcodes和例外赋过初始值了
            prev_sales = info[rpcode]
            sales = item['weixin_paid']
            info.store rpcode, (prev_sales + sales).to_f.round(2)
        end
    end
    return info

end

def updateSpreaderDailyReport date = '2022-11-24'

    sdr = getSpreaderDailyReport date

    sdr.each do |suid, info|

        uvs = [] #update strings
        cstr = info.keys.join(',')
        vals = []
        info.each do |k, v|
            uv = " #{k} = "
            val = ''
            case v.class.to_s
            when 'Integer'
                val = v.to_s
                uv += v.to_s
            when 'BigDecimal', 'Float'
                val = v.to_f.round(2).to_s
                uv += v.to_f.round(2).to_s
            when 'String'
                val = "'#{v}'"
                uv += "'#{v}'"
            else
                val = "'#{v.to_json}'"
                uv += "'#{v.to_json}'"
            end
            vals += [val]
            uvs += [uv]
        end
        vstr = vals.join(',')
        ustr = uvs.join(',')
        noon_time = Time.parse(date + ' 12:00:00').to_i #插入一个时间戳，方便进行精确的时间周期统计
        now_time = Time.now.to_i
        sql = "insert into tutem_mall.eb_spreader_daily_report(#{cstr}, spreader_uid, date, noon_time, update_time) values(#{vstr},#{suid},'#{date}', #{noon_time}, #{now_time})
        on duplicate key update #{ustr}, update_time = #{now_time}"
        queryRds sql
    end

end

def genSpreaderDailyReportSince since_date, days
    s = Date.parse(since_date)
    days.times do |d|
        puts "generating " + s.to_s
        updateSpreaderDailyReport s.to_s
        s = s.next_day
    end
end

def genSpreaderDailyReportTill till_date, days
    s = Date.parse(till_date) - days
    genSpreaderDailyReportSince(s.to_s, days)
end

def getSpreaderDailyReportRecsDuring pst, pet
    sql = "select *, first_order_count*3 + recruited_count*2 + buyer_count as pindex from tutem_mall.eb_spreader_daily_report where noon_time >= #{pst} and noon_time < #{pet} order by rep_id"
    recs = {}
    period = "[#{Time.at(pst).strftime('%Y-%m-%d %H:%M:%S')},#{Time.at(pet).strftime('%Y-%m-%d %H:%M:%S')})"
    (queryRds(sql)).each do |q|
        rec = {'period'=> period }
        rec.merge!(q)
        key = "#{q['date']}-#{q['spreader_uid']}"
        recs.store key, rec
    end
    return recs
end

#对 2023-01-27 2023-01 或者 2023这样的全部或部分日期格式，给出相应的期初时间戳
def getPeriodStartTime date_str
    start_time = date_str + ' 00:00:00'
    if date_str.size == 4 #输入的date_str只有年份
        start_time = date_str + '-01-01 00:00:00'
    elsif date_str.size == 7 #输入的date_str只有月份
        start_time = date_str + '-01 00:00:00'
    end
    return Time.parse(start_time).to_i
end

#对 2023-01-27 2023-01 或者 2023这样的全部或部分日期格式，给出相应的期末时间戳
def getPeriodEndTime date_str
    end_time = date_str + ' 24:00:00'
    if date_str.size == 4 #输入的date_str只有年份
        end_time = date_str + '-12-31 24:00:00'
    elsif date_str.size == 7 #输入的date_str只有月份
        start_time = date_str + '-01 00:00:00'
        the_time = Time.parse(start_time)
        month = the_time.month
        year = the_time.year
        next_month = month + 1
        next_month_year = year
        if next_month > 12
            next_month = 1
            next_month_year += 1
        end
        end_time = Date.new(next_month_year,next_month,1).prev_day.to_s + ' 24:00:00'
    end
    return Time.parse(end_time).to_i
end

def createRepPerformanceRec rep, pst, pet, period

        rep_id = rep['id']
        rep_code = rep['code']
        rep_team = rep['team']

        client_start_count = 0
        isql = "SELECT count(distinct user_id) as client_start_count FROM tutem_mall.eb_user_rep where rep_id = #{rep_id} and since < #{pst} and until > #{pst}"
        (queryRds(isql)).each { |i| client_start_count = i['client_start_count'] }
        client_start_list = []
        isql = "SELECT distinct user_id as client_uid FROM tutem_mall.eb_user_rep where rep_id = #{rep_id} and since < #{pst} and until > #{pst}"
        (queryRds(isql)).each { |i| client_start_list += [i['client_uid']] }

        client_end_count = 0
        isql = "SELECT count(distinct user_id) as client_end_count FROM tutem_mall.eb_user_rep where rep_id = #{rep_id} and since < #{pet} and until > #{pet}"
        (queryRds(isql)).each { |i| client_end_count = i['client_end_count'] }
        client_end_list = []
        isql = "SELECT distinct user_id as client_uid FROM tutem_mall.eb_user_rep where rep_id = #{rep_id} and since < #{pet} and until > #{pet}"
        (queryRds(isql)).each { |i| client_end_list += [i['client_uid']] }

        client_increased_count = client_end_count - client_start_count
        client_increased_list = client_end_list - client_start_list

        rep_rec = {

            'period'=>period, 'pst'=>pst, 'pet'=>pet,
            'rep_id'=>rep_id,'rep_code'=>rep_code,'rep_team'=>rep_team,
            'rep_index'=>0,                                                   #i
            'client_start_count'=>client_start_count,                         #Cs
            'client_start_list'=>client_start_list,
            'client_end_count'=>client_end_count,                             #Ce
            'client_end_list'=>client_end_list,
            'client_increased_count'=>client_increased_count,
            'client_increased_list'=> client_increased_list,

            'active_spreader_count'=>0,'active_spreader_list'=>[],            #A  活跃客户 - 有推荐新访客的客户
            'dealed_spreader_count'=>0,'dealed_spreader_list'=>[],            #C  成单客户 - 用户有购买（含复购）
            'first_order_spreader_count'=>0, 'first_order_spreader_list'=>[], #K  开单客户 - 推荐新患者成交的客户
            'new_dealed_spreader_count'=>0, 'new_dealed_spreader_list'=>[],   #S  首开客户 - 首次开单的客户
            'recruited_count'=>0,'recruited_list'=>[],                        #r
            'buyer_count'=>0,'buyer_list'=>[],                                #b
            'first_order_count'=>0,'first_order_list'=>[],                    #f
            'qualified_new_user_count'=>0, 'qualified_new_user_list'=>[],     #q
            'new_slept_count'=>0, 'new_slept_list'=>[],                       #x
            'actual_weixin_paid'=>0.00                                        #M

        }

        return rep_rec
end

def getSpecificSpreaderPerformanceDuring spreader_uid, pst, pet, duration = ''

    period = duration == '' ? "[#{Time.at(pst).strftime('%Y-%m-%d')},#{Time.at(pet).strftime('%Y-%m-%d')})" : duration
    rec = { 'spreader_uid'=>spreader_uid, 'period'=>period } # 'pst'=>pst, 'pet'=>pet }

    #增加合格首患的数据
    qualified_np_order_list = []
    sql = "select id, uid from tutem_mall.eb_store_order where
        tags like '%QPO%' and paid = 1 and pay_time >= #{pst} and pay_time < #{pet} and
        uid in (select uid from tutem_mall.eb_user where spread_uid = #{spreader_uid});"
    (queryRds(sql)).each do |q|
        oid = q['id']
        qualified_np_order_list += [oid]
    end
    qualified_np_order_count = qualified_np_order_list.size
    rec.store 'qualified_np_order_count', qualified_np_order_count
    rec.store 'qualified_np_order_list', qualified_np_order_list

    #初始化数据，这样可以处理在DailyReport中没有纪录的同志们
    default = {
            'first_order_count'=>0, 'first_order_list'=>[],
            'recruited_count'=>0, 'recruited_list'=>[],
            'buyer_count'=>0, 'buyer_list'=>[], 'order_count'=>0, 'order_list'=>[],
            'actual_weixin_paid'=>0.0, 'TTYS'=>0.0, 'TTYX'=>0.0, 'TTSZ'=>0.0
    }
    cols = default.keys
    rec = rec.merge default

    sql = "select * from tutem_mall.eb_spreader_daily_report where noon_time >= #{pst} and noon_time < #{pet} and spreader_uid = #{spreader_uid}"
    (queryRds(sql)).each do |q|
        cols.each do |key|
            value = key.include?('_list') ? JSON.parse(q[key]) : q[key] #对于[]形式的list转换为数组
            rec.store key, rec[key].nil? ? value : (rec[key] + value)
        end
    end
    rec.store 'K', rec['first_order_count']>0 ? 1 : 0
    return rec

end

#duration是一个方便用户识别的时间段名称，例如[2023-01-01,2023-02-01)可以描述为JAN23, [2022-04-01,2023-04-01)可以描述为FNY22,如果未提供则将使用标准的"[A,B)"
def getRepPerformanceDuring pst, pet, duration = ''

    period = duration == '' ? "[#{Time.at(pst).strftime('%Y-%m-%d')},#{Time.at(pet).strftime('%Y-%m-%d')})" : duration

    rep_recs = {}
    sql = "select * from tutem_mall.eb_rep where add_time < #{pet} and disabled_since > #{pst}"
    (queryRds(sql)).each do |rep|
        rep_id = rep['id']
        rep_rec = createRepPerformanceRec rep, pst, pet, period
        rep_recs.store rep_id, rep_rec
    end

    sql = "select *, first_order_count*3 + recruited_count*2 + buyer_count as pindex from tutem_mall.eb_spreader_daily_report where noon_time >= #{pst} and noon_time < #{pet}"
    (queryRds(sql)).each do |q|

        rep_id = q['rep_id']
        rep_rec = rep_recs[rep_id]

        rep_rec.store 'rep_index', rep_rec['rep_index'] + q['pindex']

        #A 活跃客户-推荐过新用户
        active_spreader_list = rep_rec['active_spreader_list']
        active_spreader_list = ( active_spreader_list + [q['spreader_uid']] ).uniq if q['recruited_count'] > 0 #if (JSON.parse(q['recruited_list'])).size > 0
        rep_rec.store 'active_spreader_count', active_spreader_list.size
        rep_rec.store 'active_spreader_list', active_spreader_list

        #D 成单客户-无论是否为新患，包括复购，有成单即可
        dealed_spreader_list = rep_rec['dealed_spreader_list']
        dealed_spreader_list = ( dealed_spreader_list + [q['spreader_uid']] ).uniq if q['order_count'] > 0
        rep_rec.store 'dealed_spreader_count', dealed_spreader_list.size
        rep_rec.store 'dealed_spreader_list', dealed_spreader_list

        #K 新开单客户 - 推荐了新患且成交
        first_order_spreader_list = rep_rec['first_order_spreader_list']
        first_order_spreader_list = ( first_order_spreader_list + [q['spreader_uid']] ).uniq if q['first_order_count'] > 0
        rep_rec.store 'first_order_spreader_count',  first_order_spreader_list.size
        rep_rec.store 'first_order_spreader_list', first_order_spreader_list

        recruited_list =  (rep_rec['recruited_list'] + JSON.parse(q['recruited_list']) ).uniq
        rep_rec.store 'recruited_count', recruited_list.size
        rep_rec.store 'recruited_list', recruited_list

        buyer_list = (rep_rec['buyer_list'] + JSON.parse(q['buyer_list'])).uniq
        rep_rec.store 'buyer_count', buyer_list.size
        rep_rec.store 'buyer_list', buyer_list

        first_order_list = (rep_rec['first_order_list'] + JSON.parse(q['first_order_list'])).uniq
        rep_rec.store 'first_order_count', first_order_list.size
        rep_rec.store 'first_order_list', first_order_list

        new_slept_list = (rep_rec['new_slept_list'] + JSON.parse(q['new_slept_list'])).uniq
        rep_rec.store 'new_slept_count', new_slept_list.size
        rep_rec.store 'new_slept_list', new_slept_list

        actual_weixin_paid = rep_rec['actual_weixin_paid'] + q['actual_weixin_paid']
        rep_rec.store 'actual_weixin_paid', actual_weixin_paid

        rep_recs.store rep_id, rep_rec

    end

    #根据现有值队列计算S和Q值
    rep_recs.each do |rep_id, rec|

        #new_dealed_spreader_count 首次开单客户：在统计周期开始之前没有推荐成交过的客户
        first_order_spreader_list = rec['first_order_spreader_list']
        new_dealed_spreader_list = first_order_spreader_list
        pst = rec['pst']
        first_order_spreader_list.each do |suid|
            sql = "select id from tutem_mall.eb_store_order where uid in ( select uid from tutem_mall.eb_user where spread_uid = #{suid} ) and pay_time < #{pst}"
            (queryRds(sql)).each do |q|
                new_dealed_spreader_list -= [suid]
            end
        end
        new_dealed_spreader_count = new_dealed_spreader_list.size
        rec.store 'new_dealed_spreader_count', new_dealed_spreader_count
        rec.store 'new_dealed_spreader_list', new_dealed_spreader_list

        #统计周期内的首单列表
        first_order_list = rec['first_order_list']
        #合格首单患者：在统计周期内出现过QPO标记的订单列表
        qualified_new_user_list = []
        pst = rec['pst']
        pet = rec['pet']
        buyers = rec['buyer_list']
        if buyers.size > 0
            #这里加上代表代码的目的是为了避免在一个很长的统计周期内，同一个客户有代表更换，导致前一个代表处发生的首单，被后继代表在大范围的统计周期内被重复统计
            sql = "select uid from tutem_mall.eb_store_order where tags like '%QPO%' and tags like '%#{rec['rep_code']}%' and paid = 1 and pay_time >= #{pst} and pay_time < #{pet} and uid in (#{buyers.join(',')});"
            (queryRds(sql)).each do |q|
                uid = q['uid']
                qualified_new_user_list += [uid]
            end
        end
        qualified_new_user_list = qualified_new_user_list.uniq
        qualified_new_user_count = qualified_new_user_list.size
        rec.store 'qualified_new_user_count', qualified_new_user_count
        rec.store 'qualified_new_user_list', qualified_new_user_list

        rep_recs.store rep_id, rec

    end

    return rep_recs

end

def update_product_shipping_tags
    sql = "select p.id, p.keyword, p.temp_id, t.name as temp_name from tutem_mall.eb_store_product p left join tutem_mall.eb_shipping_templates t on (p.temp_id = t.id)"
    usqls = []
    (queryRds(sql)).each do |q|
        id = q['id']
        temp_id = q['temp_id']
        keyword = {}
        temp_name = q['temp_name']
        begin
          keyword = JSON.parse(q['keyword']);
        rescue => e
          error_msg =  ">>>ERROR:#{Time.now.to_s} #{e}\n   update_product_shipping_tags keyword: #{q['keyword']} ProductID: #{id}\n"
          puts error_msg
          append_line_to_log error_msg, "tutem_utils.log"
        end
        keyword['merchant'] = "核心成分"
        keyword['tags'] = ["标签1","标签2"]
        keyword['template'] = "##{temp_id}#{temp_name}"
        keyword['shipping'] = "订单收到后48小时内发货，加急处理请联系客服"
        usql = "update tutem_mall.eb_store_product set keyword = '#{@rds.escape keyword.to_json}' where id = #{id}"
        usqls += [usql]
    end
    commitTrans usqls
end

def resetAllSpreadersDailyReport

    puts "reset spreader_daily_report..."

    usqls = []
    usqls += ["delete from tutem_mall.eb_spreader_daily_report;"]
    usqls += ["alter table tutem_mall.eb_spreader_daily_report auto_increment = 1;"]
    commitTrans usqls

    days = ( (Time.now.to_i - 1592070076) / 86400 ).to_i #1592070076 is the oldest user add_time in system
    genSpreaderDailyReportTill Date.today.to_s, days

    puts "done"

end

def confirmQualifiedNewPatientsFor uid, oid, mark = ''
    puts "QNP confirmed for U##{sprintf '%05d', uid} O##{sprintf '%05d', oid}#{mark}"
    usqls = ["update tutem_mall.eb_user set is_qp = 1 where uid = #{uid}"]
    usqls += ["update tutem_mall.eb_store_order set tags = concat(tags, ' QPO') where id = #{oid} and tags not like '%QPO%'"]
    commitTrans usqls
end

def confirmUnqualifiedNewPatientsFor uid
    puts "UNP marked for U##{sprintf '%05d',uid}"
    usqls = ["update tutem_mall.eb_user set is_qp = -1 where uid = #{uid}"]
    commitTrans usqls
end

def leavePatientDecidedFuther uid
    #puts "DFP for U##{sprintf '%05d', uid} - leave patient to be decided further"
end

#对属于待判合格新患的用户，对订单进行统计，更新用户标识及相关订单:
#在eb_user表中需要一个位置说明是否为合格首患is_qp，-1 不是， 0， 待判断， 1：是
#如果首单日期+28天内共购买了28个标准服用量或以上
#   则判断为合格新患
#否则
#   如果首单日期+28天小于当前日期（也就是过期了）
#       判断为非合格新患
#   否则 #尚未逾期
#       保持待判断标识
def updateQP default_condition = ' is_qp = 0 and skip_np = 0'

    extend_days = 28  #自首单日计算合格购买量的天数范围
    served_units = 28  #四周的量

    qp_uids = [] #Qualified New Patient
    uq_uids = [] #Not Qualified New Patient
    nd_uids = [] #Undecided Patient

    sql = "select id, uid, pay_time, tags from tutem_mall.eb_store_order where paid = 1 and tags like '%NPO%' and uid in (select uid from tutem_mall.eb_user where #{default_condition} )"

    (queryRds(sql)).each do |q|

        uid = q['uid']
        #next if uid != 1

        fo_id = q['id']

        fo_pay_time = q['pay_time']
        fo_tags = q['tags'].split(' ')
        fo_pay_date = Time.at(fo_pay_time).to_date
        pst = fo_pay_time
        pet = fo_pay_time + extend_days * 24 * 60 * 60
        sql2 = "select p.oid, p.uid, p.pcode_info from tutem_mall.eb_pickup_book p left join tutem_mall.eb_store_order o on (p.oid = o.id) where p.uid = #{uid} and o.pay_time >= #{pst} and o.pay_time <= #{pet} order by pay_time asc;"
        served_days = 0
        qco_id = 0 #QNP Confirm Order
        (queryRds(sql2)).each do |r|
            pcode_info = JSON.parse r['pcode_info']
            pcode_info.each do |pcode,num|
                days = getStandardServeDaysForPcode pcode
                served_days += days * num
                #puts "o##{r['oid']} pcode#{pcode} days#{days} num#{num} total served: #{served_days}" #debug
                qco_id = r['oid'] if served_days >= served_units && qco_id == 0 #qco_id==0保证只取第一次满足的订单号
            end
        end
        if qco_id > 0       #满足条件三
            mark = qco_id == fo_id ? '' : "*"
            confirmQualifiedNewPatientsFor uid, qco_id, mark
            qp_uids += [uid]
        else                #不满足条件三
            if Time.now.to_i > pet
                confirmUnqualifiedNewPatientsFor uid
                uq_uids += [uid]
            else
                leavePatientDecidedFuther uid
                nd_uids += [uid]
            end
        end

    end

    prompt  = "#{qp_uids.size} New Qualified Patient founded: [#{qp_uids.join(',')}]\n"
    prompt += "#{uq_uids.size} UnQaulified New Patient founded: [#{uq_uids.join(',')}]\n"
    prompt += "#{nd_uids.size} Not Decided New Patient remained: [#{nd_uids.join(',')}]\n"
    puts prompt

    res = {}
    res.store 'QP', qp_uids
    res.store 'UQ', uq_uids
    res.store 'ND', nd_uids

    return res

end

def fixQPBugs
    for_uids = " uid in (select uid from tutem_mall.eb_user where is_qp = 1 and skip_np = 0) and uid not in (select uid from tutem_mall.eb_store_order where tags like '%QPO%')"
    recs = updateQP for_uids
    text = "[#{Time.now.strftime('%Y-%m-%d %H:%M:%S')}] #{recs.to_json}\n"
    append_lines_to_fn [text], 'fixQPBugs.log', 'fix'
end

def getMonthlyRepPerformanceDuring start_date = Date.parse('2020-12-06'), end_date = Time.now.to_date

    current_date = start_date
    current_month = start_date.month
    current_year = start_date.year

    recs = {}

    while current_date < end_date do

        # 处理当前月份
        current_month_start_date = current_date
        # 计算下一个月份
        current_month += 1
        if current_month > 12
            current_month = 1
            current_year += 1
        end
        current_month_end_date = Date.new(current_year, current_month, 1)

        pst = Time.parse(current_month_start_date.to_s + ' 00:00:00').to_i
        pet = Time.parse(current_month_end_date.to_s + ' 00:00:00').to_i
        duration = current_date.strftime('%y%b') #23Feb
        rp = getRepPerformanceDuring pst, pet, duration
        rp.each { |rep_id, rec| recs.store "#{duration}-#{rep_id}", rec }

        current_date = current_month_end_date

    end

    return recs

end

def getRepPerformanceRecordKeys
    recs = getRepPerformanceDuring Time.parse('2023-04-01 00:00:00').to_i, Time.parse('2023-05-1 00:00:00').to_i, '23Apr'
    recs.each do |rep_id, rep_rec|
        return rep_rec.keys if rep_id == 0
    end
    return []
end

def formatRepPerformanceRecsInText rep_recs, format, team_filter='', rep_filter='', sort_field = 'rep_id', sort_by = 'asc'

    recs = (rep_recs.sort_by { |rep_id, rec| rec[sort_field] })
    recs = recs.reverse if sort_by == 'desc'

    text = ''
    return text if recs.empty?

    sample = {}
    recs.each { |rep_id, rep_rec| sample = rep_rec; break}
    from = Time.at(sample['pst']).to_date.to_s
    till = Time.at(sample['pet']).to_date.to_s

    #为统计数据生成结构并初始化其值

    text = "[#{from},#{till})\n"

    #生成文本表头, 生成汇总的初始结构
    summary = {}
    format.each do |key, fmt|
        #col = sprintf '%-5s',fmt['abbrv'] #右边补齐
        case fmt['class']
        when 'Integer'
            summary.store key, 0
        when 'BigDecimal', 'Float'
            summary.store key, 0.0
        else
            summary.store key, 'N/A'
        end
        text += fmt['abbrv'] + ' '
    end
    #text += "\n"
    text += "\n––––––––––––––\n"

    #文本内容
    recs.each do |rep_id, rec|
        next if team_filter != '' && rec['rep_team'] != team_filter
        next if rep_filter != '' && rec['rep_code'] != rep_filter
        #根据format规定的格式和顺序，输出rec的部分内容
        format.each do |key, v |
            val = rec[key]
            #case val.class.to_s
            case v['class']
            when 'Integer'
                if v['width'].nil?
                    col = val.to_i.to_s
                else
                    fmt = "%0#{v['width']}d"
                    col = sprintf(fmt, val)
                end
                summary.store key, summary[key] + val
            when 'String'
                col = val.to_s
            when 'BigDecimal', 'Float'
                col = sprintf('%2.2f',val.to_f.round(2))
                summary.store key, summary[key] + val
            else
                col = val.to_json
            end
            text += "#{col} "
        end
        text += "\n"
    end
    text += "––––––––––––––\n"

    first_col = true
    summary.each do |key, val|
        #next if format[key]['display'] == 0
        next if format[key].nil?
        if first_col
            val = 'SUM'
            first_col = false
        end
        case val.class.to_s
        when 'Integer'
            col = val.to_i.to_s
        when 'String'
            col = val.to_s
        when 'BigDecimal', 'Float'
            col = val.to_f.round(2).to_s
        else
            col = val.to_json
        end
        text += "#{col} "
    end
    text += "\n"

    return text

end

#TBO 这一部分将来要从数据库中取得
def getRepKpiTarget rep_id, month

    #注意%b格式输出时有大小写
    kts = {
        16 => {'23Mar' => 10.25, '23Apr' =>  6.25, '23May' => 5.25 , '23Jun' => 5.25, '23Jul' => 5.25},   #FC1
        19 => {'23Mar' => 5.25, '23Apr' =>  6.25, '23May' => 5.25, '23Jun' => 5.25, '23Jul' => 5.25 },    #LXH
        14 => {'23Mar' => 12.25, '23Apr' =>  8.25, '23May' => 7.25, '23Jun' => 7.25, '23Jul' => 7.25 },   #LY1
        22 => {'23Mar' => 1.25, '23Apr' =>  2.25, '23May' => 1.25, '23Jun' => 1.25, '23Jul' => 1.25 },    #LZH
        23 => {'23Mar' => 0.25, '23Apr' =>  1.25, '23May' => 0.25, '23Jun' => 0.25, '23Jul' => 0.25 },    #PJQ
        10 => {'23Mar' => 22.25, '23Apr' => 20.25, '23May' => 19.25, '23Jun' => 19.25, '23Jul' => 19.25 },  #PRB
        20 => {'23Mar' => 4.25, '23Apr' =>  5.25, '23May' => 4.25, '23Jun' => 4.25, '23Jul' => 4.25 },    #SYJ
        8 => {'23Mar' => 11.25, '23Apr' =>  5.25, '23May' => 4.25, '23Jun' => 4.25, '23Jul' => 4.25 },    #WJH
        18 => {'23Mar' => 6.25, '23Apr' =>  5.25, '23May' => 4.25, '23Jun' => 4.25, '23Jul' => 4.25 }     #ZHZ
    }

    return 0.0 if kts[rep_id].nil?
    return 0.0 if kts[rep_id][month].nil?
    return kts[rep_id][month]

end

def  calcKpiScoreFor rec

    rep_id = rec['rep_id']
    pst = rec['pst']
    pet = rec['pet']
    days = (pet - pst)/(24*60*60)
    month = Time.at(pst).strftime '%y%b'

    rkt = getRepKpiTarget rep_id, month
    return 0 if rkt == 0

    k = rec['first_order_spreader_count']
    f = rec['first_order_count']
    q = rec['qualified_new_user_count']

    kpi_1 = k / rkt
    kpi_1 = 1.4 if kpi_1 > 1.4
    kpi_1 = kpi_1 * 25

    kpi_2 = f == 0 ? 0 : f / rkt / 4
    kpi_2 = 1.4 if kpi_2 > 1.4
    kpi_2 = kpi_2 * 50

    kpi_3 = ( f == 0 || q == 0 ) ? 0 : (q.to_f/f.to_f)/0.6 #如果直接用3/5，则得零
    kpi_3 = 1.4 if kpi_3 > 1.4
    kpi_3 = kpi_3 * 25

    kpi = kpi_1 + kpi_2 + kpi_3

    return kpi.to_f.round(2)

end

def defaultMonthlyKpiFormat
    return  {
        "rep_code"=> {"class"=>'String',"abbrv"=>"REP"},
        "client_end_count"=> {"class"=>'Integer',"abbrv"=>"C"},
        'active_spreader_count' => { 'class'=>'Integer', 'abbrv' => 'A', 'width' => 1}, #A 活跃客户数
        'new_dealed_spreader_count'=> {'class'=>'Integer', 'abbrv'=>'S', 'width' => 1}, #S 新激活客户
        "first_order_spreader_count"=> {"class"=>'Integer',"abbrv"=>"K", 'width' => 1}, #开单客户；有首患成交
        "first_order_count"=> {"class"=>'Integer',"abbrv"=>"f", 'width' => 1},
        "qualified_new_user_count"=> {"class"=>'Integer',"abbrv"=>"q", 'width' => 1},
        "actual_weixin_paid"=> {"class"=>'BigDecimal',"abbrv"=>"Sales"},
        #"rkt"=> {"class"=>"BigDecimal","abbrv"=>"Target"},
        #"kpi"=> {"class"=>'Integer', 'abbrv'=>'kpi'},
        #"flg"=> {"class"=>"String", 'abbrv'=>'进展'}
        #"est"=> {"class"=>'Integer','abbrv'=>'E'},
    }

end

def genMonthlySalesKpiTillDay day = Date.today.prev_day.to_s
    year = day[0..3]
    date = Date.parse(day)
    yb_month = date.strftime('%y%b') #月份缩写

    txt_fn =  OS.windows? ? ".\\nginx\\html\\kpi\\#{year}\\Monthly-Sales-KPI-#{yb_month}.txt" : "./nginx/html/kpi/#{year}/Monthly-Sales-KPI-#{yb_month}.txt" #SalesRepKpiReport
    xls_fn =  OS.windows? ? ".\\nginx\\html\\kpi\\#{year}\\Monthly-Sales-KPI-#{yb_month}.xls" : "./nginx/html/kpi/#{year}/Monthly-Sales-KPI-#{yb_month}.xls" #SalesRepKpiReport
    xls_url = "http://oms2.totembio.cn/kpi/#{year}/Monthly-Sales-KPI-#{yb_month}.xls"

    last_day_of_month = Date.new(date.year, date.month, -1)
    first_day_of_month = Date.new(date.year, date.month, 1)

    #按时间进度估算比例
    percent = ( ( (date - first_day_of_month) + 1 )  / (last_day_of_month - first_day_of_month + 1) ).to_f.round(2)
    percent = 0 if percent < 0
    percent = 1.0 if percent > 1.0

    pst = Time.parse("#{first_day_of_month.to_s} 00:00:00").to_i
    pet = Time.parse("#{last_day_of_month.to_s} 24:00:00").to_i
    pet = Time.parse("#{day} 24:00:00").to_i

    original_recs = getRepPerformanceDuring pst, pet, yb_month
    #在recs中叠加kpi
    recs = {}
    original_recs.each do |rep_id, rec|
        rkt = getRepKpiTarget rep_id, yb_month  #目标值
        rec.store 'rkt', rkt
        kpi = calcKpiScoreFor rec
        rec.store 'kpi', kpi
        est = kpi / percent
        flag = "<font color='comment'>加油</font>"
        flag = "及格" if est >= 60
        flag = "<font color='warning'>良好</font>" if est >= 80
        flag = "<font color='green'>优秀</font>" if est >= 100
        #rec.store 'flg', flag
        recs.store rep_id, rec
    end
    dump_hash_to_excel recs, xls_fn, false

    format = {
        "rep_code"=> {"class"=>'String',"abbrv"=>"REP"},
        'active_spreader_count' => { 'class'=>'Integer', 'abbrv' => 'A', 'width' => 2}, #A 活跃客户数
        "recruited_count"=> {"class"=>'Integer',"abbrv"=>"r"}, #推荐用户数
        'new_dealed_spreader_count'=> {'class'=>'Integer', 'abbrv'=>'S', 'width' => 1}, #S 新激活客户
        #"client_end_count"=> {"class"=>'Integer',"abbrv"=>"C"},
        "first_order_spreader_count"=> {"class"=>'Integer',"abbrv"=>"K", 'width' => 1}, #开单客户；有首患成交
        "first_order_count"=> {"class"=>'Integer',"abbrv"=>"f", 'width' => 1},
        "qualified_new_user_count"=> {"class"=>'Integer',"abbrv"=>"q", 'width' => 1},
        #"actual_weixin_paid"=> {"class"=>'BigDecimal',"abbrv"=>"M"},
        "rkt"=> {"class"=>"BigDecimal","abbrv"=>"T"},
        "kpi"=> {"class"=>'Integer', 'abbrv'=>'kpi'},
        #"flg"=> {"class"=>"String", 'abbrv'=>'进展'}
        #"est"=> {"class"=>'Integer','abbrv'=>'E'},
    }

    report = "诊疗销售月度推广绩效简报\n"
    report += formatRepPerformanceRecsInText recs, format, 'SALES', '', 'rep_id', 'asc'
    report += "\n"
    report += ">#{Time.now.strftime('%Y-%m-%d %H:%M%S')}\n"
    report += "[数据详情](#{xls_url}) [指标说明](https://oms2.totembio.cn/kpi/kpi_memo.txt)\n"

    File.open(txt_fn,"w+:UTF-8") { |f| f.write report }

    return report

end

def genDailySalesPerformanceFor day = Date.today.prev_day.to_s

    year = day[0..3]
    month = day[0..6]

    txt_fn =  OS.windows? ? ".\\nginx\\html\\kpi\\#{year}\\Daily-Sales-Performance-#{day}.txt" : "./nginx/html/kpi/#{year}/Daily-Sales-Performance-#{day}.txt" #SalesRepKpiReport
    xls_fn =  OS.windows? ? ".\\nginx\\html\\kpi\\#{year}\\Daily-Sales-Performance-#{day}.xls" : "./nginx/html/kpi/#{year}/Daily-Sales-Performance-#{day}.xls" #SalesRepKpiReport
    xls_url = "http://oms2.totembio.cn/kpi/#{year}/Daily-Sales-Performance-#{day}.xls"

    pst = Time.parse(day.to_s + ' 00:00:00').to_i
    pet = Time.parse(day.to_s + ' 24:00:00').to_i

    recs = getRepPerformanceDuring pst, pet, day.to_s
    dump_hash_to_excel recs, xls_fn, false

    format = {
        "rep_code"=> {"class"=>'String',"abbrv"=>"REP"},
        #"client_end_count"=> {"class"=>'Integer',"abbrv"=>"C", 'width'=>3},
        "client_increased_count"=> {"class"=>'Integer',"abbrv"=>"I"},
        "active_spreader_count"=> {"class"=>'Integer',"abbrv"=>"A"},
        "new_dealed_spreader_count"=> {"class"=>'Integer',"abbrv"=>"S"}, #首单客户
        "first_order_spreader_count"=> {"class"=>'Integer',"abbrv"=>"K"},#开单客户；有首患成交
        "recruited_count"=> {"class"=>'Integer',"abbrv"=>"r"},
        "first_order_count"=> {"class"=>'Integer',"abbrv"=>"f"},
        "qualified_new_user_count"=> {"class"=>'Integer',"abbrv"=>"q"}
    }

    report = "诊疗销售每日推广绩效简报\n"
    report += formatRepPerformanceRecsInText recs, format, 'SALES', ''
    report += "\n"
    report += ">#{Time.now.strftime('%Y-%m-%d %H:%M:%S')}\n"
    report += "[数据详情](#{xls_url}) [指标说明](https://oms2.totembio.cn/kpi/kpi_memo.txt)\n"

    File.open(txt_fn,"w+:UTF-8") { |f| f.write report }

    return report

end

def genDailyUserReport day = Date.today.prev_day.to_s
    date = Date.parse(day)
    fn =  OS.windows? ? ".\\nginx\\html\\users\\Daily-User-Report-#{day}.txt" : "./nginx/html/users/Daily-User-Report-#{day}.txt"
    url = "https://oms2.totembio.cn/users/Daily-User-Report-#{day}.txt"
    report = "# 每日用户情况简报\n"
    pst = Time.parse(date.to_s + ' 00:00:00').to_i
    pet = Time.parse((date + 1).to_s + ' 00:00:00').to_i
    report += genUserReportDuring pst, pet
    report += "\n"
    report += ">#{Time.now.strftime('%Y-%m-%d %H:%M:%S')}\n"
    report += "[下载文本](#{url})\n"
    File.open(fn,"w+:UTF-8") { |f| f.write report }
    return report
end

def genWeeklyUserReport day = Date.today.prev_day.to_s
    date = Date.parse(day)
    fn =  OS.windows? ? ".\\nginx\\html\\users\\Weekly-User-Report-#{day}.txt" : "./nginx/html/users/Weekly-User-Report-#{day}.txt"
    url = "https://oms2.totembio.cn/users/Weekly-User-Report-#{day}.txt"
    report = "# 每周用户情况简报\n"
    pst = Time.parse((date-6).to_s + ' 00:00:00').to_i
    pet = Time.parse((date+1).to_s + ' 00:00:00').to_i
    report += genUserReportDuring pst, pet
    report += "\n"
    report += ">#{Time.now.strftime('%Y-%m-%d %H:%M:%S')}\n"
    report += "[下载文本](#{url})\n"
    File.open(fn,"w+:UTF-8") { |f| f.write report }
    return report
end

#早上的七点钟的定时发送程序
def sendMorningReports day = Date.today.prev_day.to_s, always = false, send = true

    morning_report_flag =  OS.windows? ? ".\\nginx\\html\\kpi\\mrf-#{day}.txt" : "./nginx/html/kpi/mrf-#{day}.txt" #SalesRepKpiReport
    if ( !(FileTest::exist? morning_report_flag) && Time.now.hour >= 6 ) || always

        File.open(morning_report_flag, "a+:UTF-8") { |f| f.write "reported on #{Time.now.to_s}\n" }

        report = genDailyUserReport
        send_message_to_bot 'WWBOT_TT', report, ['@all'] if send
        puts "#{report}\n"

        if false && Date.today.sunday? #仅当周末时才运行 #暂时不再运行
            report = genWeeklyUserReport
            send_message_to_bot 'WWBOT_TT', report, ['@all'] if send
            puts "#{report}\n"
        end

        report = genDailySalesPerformanceFor day
        send_message_to_bot 'WWBOT_TT', report, ['@all'] if send
        puts "#{report}\n"

        report = genMonthlySalesKpiTillDay day
        send_message_to_bot 'WWBOT_TT', report, ['@all'] if send
        puts "#{report}\n"

    end

end

#生成按月统计的所有代表业绩历史数据，这里的总实际销售额与后台统计之间差一天
def genMonthlyRepPerformanceTillNow

    fn = "monthly-rep-performance-full.xls"

    start_date = Date.parse('2020-10-01'); end_date = Time.now.to_date;
    recs = getMonthlyRepPerformanceDuring start_date, end_date
    dump_hash_to_excel recs, fn, false

    #recs = get_hash_from_titled_xlsx fn
    period_sum = {}
    recs.each do | idx, rec |
        period = rec['period']
        sales = rec['actual_weixin_paid']
        period_sum.store period, 0.00 if period_sum[period].nil?
        period_sum.store period, ( period_sum[period] + sales ).to_f.round(2)
    end

    return period_sum

end
#genMonthlyRepPerformanceTillNow

def getChannelsByRepCode rep_id
    sql = "select * from tutem_mall.eb_user where rep_id = #{rep_id}"

end

#createMonthlySalesForecastOn 2023, 5 #数据是4月份的
def createMonthlySalesForecastOn year, month

    first_day_of_new_month = Date.new(year,month,1)
    last_day_of_last_month = first_day_of_new_month - 1
    first_day_of_last_month = Date.new(last_day_of_last_month.year, last_day_of_last_month.month, 1)
    duration = first_day_of_last_month.strftime('%y%b')

    pst = Time.parse(first_day_of_last_month.to_s + ' 00:00:00').to_i
    pet = Time.parse(last_day_of_last_month.to_s + ' 24:00:00').to_i

    #get reps
    reps = {}
    sql = "select id, code from tutem_mall.eb_rep where disabled_since > #{pet} and team = 'SALES'"
    (queryRds(sql)).each do |q|
        rep_id = q['id']
        rep_code = q['code']
        reps.store rep_id, {'rep_id'=>rep_id, 'rep_code'=>rep_code}
    end

    #for each rep get his channels info
    reps.each do |rep_id, rep|

        recs = {}
        sql = "select uid as spreader_uid from tutem_mall.eb_user where rep_id = #{rep_id}"
        (queryRds(sql)).each do |q| 
            spreader_uid = q['spreader_uid']
            #渠道信息
            rec = {
                "spreader_uid"=>spreader_uid,
                "spreader_name"=>userList[spreader_uid]['name'],
                "spreader_agency"=>userList[spreader_uid]['agency'],
                "tupay"=>userList[spreader_uid]['now_money'],
                #"spreader_deparment"=>userList[spreader_uid]['department'],
                #"spreader_type"=>userList[spreader_uid]['spreader_type']
            }
            #增加spreader_daily报告中的数据
            rec = rec.merge ( getSpecificSpreaderPerformanceDuring spreader_uid , pst, pet, duration )
            #增加一个排序指数
            spreader_index = (rec['recruited_count'] + rec['actual_weixin_paid']/1000 + rec['first_order_count']*3 + rec['qualified_np_order_count']*4).to_i
            rec.store 'spreader_index', spreader_index
            recs.store spreader_uid, rec
        end

        recs = (recs.sort_by { |spreader_uid, rec| - rec['spreader_index'] }).to_h
        dump_hash_to_excel recs, "#{get_dir_path('sales-report')}Monthly-Sales-Forecast-#{first_day_of_new_month.strftime('%y%b')}-#{rep_id}-#{rep['rep_code']}"

    end

end

def syncUserData uid
    puts "sync user data for ##{uid}"
    update_pickup_goods_info_by uid
    updateUserInfo uid
end

